# _*_ coding: utf-8 _*_
from odoo import api, fields, models, tools


class StockInoutDetialHe(models.Model):
    _name = "stock.inout.detail.he"
    _auto = False
    # _order = 'create_date asc'

    # move_id = fields.Many2one('stock.move', u'Stock Move', required=True)
    # location_id = fields.Many2one('stock.location', 'Location', required=True)
    product_id = fields.Many2one('product.product', '产品')
    location_id = fields.Many2one('stock.location', '库存位置')
    qty = fields.Float('数量')
    write_date = fields.Date('出入库日期')
    force_date = fields.Date('强制日期')
    # create_date = fields.Datetime('创建日期')
    # serial_number = fields.Char('追踪号', required=True)
    # origin = fields.Char(u'源单据', required=True)
    # picking_type_id = fields.Many2one('stock.picking.type',
    #                                   string=u'拣货类型')
    type_name = fields.Char(string=u'拣货类型')
    origin = fields.Char(string=u'源单据')
    order_name = fields.Char(string=u'单号')
    lot_id = fields.Many2one('stock.production.lot', string='追踪号/批次号')
    product_uom_id = fields.Many2one('product.uom', string='单位')

    # @api.model_cr
    def init(self):
        tools.drop_view_if_exists(self._cr, 'stock_inout_detail_he')
        self._cr.execute("""
                CREATE VIEW stock_inout_detail_he AS (
                  select row_number() over() as id,* from (SELECT p.name as order_name,p.origin,l.write_date,p.force_date,o.product_id AS product_id, o.product_uom_id AS product_uom_id, o.location_id AS location_id, l.qty * -1 AS qty,
                         l.lot_id AS lot_id, t.name AS type_name
                    FROM stock_pack_operation o
                        JOIN stock_pack_operation_lot l ON l.operation_id = o.id
                        JOIN stock_picking p ON p.id = o.picking_id
                        JOIN stock_location k ON k.id = o.location_id
                        JOIN stock_picking_type t ON t.id = p.picking_type_id
                    WHERE p.state = 'done'
                        AND k.usage = 'internal'
                    UNION
                    SELECT p.name as order_name,p.origin,l.write_date,p.force_date,o.product_id, o.product_uom_id, o.location_dest_id, l.qty, l.lot_id,t.name
                    FROM stock_pack_operation o
                        JOIN stock_pack_operation_lot l ON l.operation_id = o.id
                        JOIN stock_picking p ON p.id = o.picking_id
                        JOIN stock_location k ON k.id = o.location_dest_id
                        JOIN stock_picking_type t ON t.id = p.picking_type_id
                    WHERE p.state = 'done'
                        AND k.usage = 'internal'
                    UNION
                    SELECT p.name as order_name,p.origin,l.write_date,p.mo_done_date,l.product_id, m.product_uom, m.location_id, quantity_done*-1, l.lot_id, t.name
                    FROM stock_move_lots l
                        JOIN stock_move m ON m.id = l.move_id
                        JOIN mrp_production p ON p.id = m.raw_material_production_id
                        JOIN stock_location k ON k.id = m.location_id
                        JOIN stock_picking_type t ON t.id = p.picking_type_id
                    WHERE m.state = 'done'
                        AND k.usage = 'internal'
                    UNION
                    SELECT p.name as order_name,p.origin,l.write_date,p.mo_done_date,l.product_id, m.product_uom, m.location_dest_id, quantity_done, l.lot_id, t.name
                    FROM stock_move_lots l
                        JOIN stock_move m ON m.id = l.move_id
                        JOIN mrp_production p ON p.id = m.production_id
                        JOIN stock_location k ON k.id = m.location_dest_id
                        JOIN stock_picking_type t ON t.id = p.picking_type_id
                    WHERE m.state = 'done'
                        AND k.usage = 'internal'
                    UNION
                    SELECT i.name as order_name,'',l.write_date,i.accounting_date,l.product_id, l.product_uom_id, l.location_id, product_qty - theoretical_qty, l.prod_lot_id, '盘点出库' AS name
                    FROM stock_inventory_line l
                        JOIN stock_inventory i ON i.id = l.inventory_id
                    WHERE i.state = 'done'
                        AND theoretical_qty > product_qty
                    UNION
                    SELECT i.name as order_name,'',l.write_date,i.accounting_date,l.product_id, l.product_uom_id, l.location_id, product_qty - theoretical_qty, l.prod_lot_id, '盘点入库' AS name
                    FROM stock_inventory_line l
                        JOIN stock_inventory i ON i.id = l.inventory_id
                    WHERE i.state = 'done'
                        AND theoretical_qty < product_qty
                    UNION
                    SELECT s.name,'',s.date,NULL,sl.product_id,t.uom_id,sl.src_location_id,sl.product_qty*-1,sl.lot_id,'库存上架' as type_name
                    FROM stock_put_on_shelf s join stock_put_on_shelf_line sl on s.id=sl.shelf_id
                        JOIN product_product p on sl.product_id=p.id
                        JOIN product_template t on t.id=p.product_tmpl_id WHERE s.state='done'
                    UNION
                    SELECT s.name,'',s.date,NULL,sl.product_id,t.uom_id,sl.dest_location_id,sl.product_qty,sl.lot_id,'库存上架' as type_name
                    FROM stock_put_on_shelf s join stock_put_on_shelf_line sl on s.id=sl.shelf_id
                        JOIN product_product p on sl.product_id=p.id
                        JOIN product_template t on t.id=p.product_tmpl_id WHERE s.state='done')as sst
                )""")
